/*
 Data loader for spreadsheet under GNU general public liscence
 */
/**
 *
 * @author lanchana
 * @DataLoader to the spreadsheet
 * @email:lanch.gunawardhana@gmail.com
 */
package DBHandler;

import MsgViewer.MessageBox;
import java.awt.Checkbox;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
import java.util.ArrayList;

public class DBHandler
{
    public ArrayList<String> getTableNames()//getting all table names in the selected database into an arraylist
	{
		ArrayList<String> tableNameList=new ArrayList<String>();
		try
		{
			DatabaseMetaData metaData=DBConnector.getConnectionInstance().getMetaData();
			ResultSet rs=metaData.getTables(null, null, null, new String[]{"TABLE"});

			while (rs.next())
            {
				tableNameList.add(rs.getString("TABLE_NAME"));
			}
            return  tableNameList;

		}
		catch (Exception e) {
			// TODO: handle exception
			System.err.println("Cannot get table names, "+e.getMessage());
			return null;
		}
    }

    public ArrayList<String> getFieldName(String tableName)//get all field names in the selected tables and add them to an arraylist
    {

      ArrayList<String> fields=new ArrayList<String>();
      try
      {
            Statement st = DBConnector.getConnectionInstance().createStatement();
            ResultSet rs = st.executeQuery("SELECT * FROM " + tableName);
            ResultSetMetaData md = rs.getMetaData();
            int col = md.getColumnCount();
            System.out.println("Number of Column : "+ col);
            System.out.println("Columns Name: ");
            for (int i = 1; i <= col; i++)
            {
                 fields.add(md.getColumnName(i));
            }
            return fields;

      }
      catch (Exception e)
      {
           new MessageBox().displayMsg("ERROR","Some of the fields in your database are null.Check the database");
           return null;
      }

    }

    public ArrayList<Object[]> getTableData(String tableName,int columnCount)//get table data from the database and add them rowwise
    {
      ArrayList<Object[]> data=new ArrayList<Object[]>();
      try
      {
            Statement st = DBConnector.getConnectionInstance().createStatement();
            ResultSet rs = st.executeQuery("SELECT * FROM " + tableName);
            int j=0;
            while(rs.next())
            {
                Object[] array=new Object[columnCount];
                for(int i=1;i<=columnCount;i++)
                {
                   
                    array[i-1]=rs.getObject(i);
                    j++;
                }
                data.add(array);
            }

            return data;
      }
      catch (Exception e)
      {
            new MessageBox().displayMsg("ERROR","some of the data in your table are null.Loading a null value to the excel sheet may cause errors");
            return null;
      }
    }


}
